/* 
 * JUGJobs -- A Jobs Posting Application for Java Users Groups and Other Groups
 * $Id: Database.java,v 1.6 2006/06/26 05:01:45 biglee Exp $
 * 
 * ***** BEGIN LICENSE BLOCK *****
 * Version: MPL 1.1
 *
 * The contents of this file are subject to the Mozilla Public License Version
 * 1.1 (the "License"); you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 * http://www.mozilla.org/MPL/
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 *
 * The Original Code is the JUGJobs project.
 *
 * The Initial Developers of the Original Code are the members of the Triangle
 * Java User's Group in the RTP area of North Carolina.
 * Portions created by the Initial Developer are Copyright (C) 2005
 * the Initial Developers. All Rights Reserved.
 *
 * Contributor(s):
 *
 * ***** END LICENSE BLOCK ***** 
 */
package org.jugjobs.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.http.HttpServlet;

import org.jugjobs.controller.Logger;
import org.jugjobs.form.JobBean;

/**
 * @author BigLee
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
class Database {
	  static String tableName;
	  static String userName;
	  static String password; 
	  static String connName;

	  static String driverName = null;
	  static String dbUrl;
	  static String hostName;
	  static String databaseName;

	  static Logger logger = new Logger();
	  
	  private Database() {
	  }
	  
	  public Database(HttpServlet servlet) {
		    driverName = servlet.getInitParameter("dbDriverName");
		    logger.log("Driver Name: "+driverName);
		    dbUrl = servlet.getInitParameter("dbUrl");
		    logger.log("Database URL: "+dbUrl);
		    hostName = servlet.getInitParameter("dbHostName");
		    logger.log("Host Name: "+hostName);
		    databaseName = servlet.getInitParameter("dbDatabaseName");
		    logger.log("Database Name: "+databaseName);
		    tableName = servlet.getInitParameter("dbTableName");
		    logger.log("Table Name: "+tableName);
		    userName = servlet.getInitParameter("dbUserName");
		    logger.log("User: "+userName);
		    password = servlet.getInitParameter("dbPassword");
		    logger.log("Password: "+ password.length()+" chars");
		    connName = dbUrl + hostName+"/"+databaseName+"?zeroDateTimeBehavior=convertToNull";
		    logger.log("Connect String: "+connName);
	  }
	  
	  public Connection connectToDb()throws SQLException {
	    try {
	      Class.forName(driverName).newInstance();
	      logger.log("Driver Registered Successfully.");
	    } catch (Exception e) {
	      logger.log(e.toString());
	      throw new RuntimeException(
	        "Exception in Class.forName(driverName).newInstance(); " +
	          e.toString());
	    }
	    Connection c = DriverManager.getConnection(connName,userName,password);
	    return c;
	  }
	  
	  public JobBean getPosting(int jobNumber) throws SQLException{
	    Connection connection = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    try{
	      connection = connectToDb();
	      ps = connection.prepareStatement(
	                    "SELECT * FROM " + tableName + " WHERE job_number = ?");
	      ps.setInt(1,jobNumber);
	      rs = executeForOneRow(ps);
	      return fetchBeanFields(rs);
	    }
	    finally{
	      if(rs!=null)try{rs.close();}catch(Exception e){};
	      if(ps!=null)try{ps.close();}catch(Exception e){};
	      if(connection!=null)connection.close();;
	    }
	  }

	  //returns an ArrayList of JobBean
	  public ArrayList getAllPostings() throws SQLException{
	    Connection connection = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    try{
	      logger.log("about to connect");
	      connection = connectToDb();
	      logger.log("connected");
	      ps = connection.prepareStatement(
	                "SELECT * FROM " + tableName + " ORDER BY job_number DESC");
	      rs = ps.executeQuery();
	      ArrayList postingsList = new ArrayList();
	      while(rs.next()){
	      	JobBean formBean = fetchBeanFields(rs);
	      	formBean.setArrayPosition(postingsList.size());
	        postingsList.add(formBean);
	      }
	      return postingsList;
	    }
	    catch (SQLException e) {
	    	logger.log(e);
	    	throw(e);
	    }
	    finally{
	      if(rs!=null)try{rs.close();}catch(Exception e){};
	      if(ps!=null)try{ps.close();}catch(Exception e){};
	      if(connection!=null)connection.close();
	    }
	  }

	  
	  public void deletePosting(Connection connection, int jobNumber)
	                        throws SQLException{
	    PreparedStatement ps = connection.prepareStatement(
	                "DELETE FROM " + tableName + " WHERE job_number=?");
	    ps.setInt(1,jobNumber);
	    executeUpdate(ps);
	  }
	  
	  public void insertPosting(Connection connection, JobBean entryForm) 
	                        throws SQLException{
	    PreparedStatement ps = connection.prepareStatement(
	      "INSERT " + tableName + 
	        "(job_title, job_desc, employer_name, employer_url, contact_name, " +
	        "contact_info, posting_password, last_edit_date, admin_contact_info)" +
	        " VALUES(?,?,?,?,?,?,?,?,?)");
	    ps.setString(1,entryForm.getJob_title());
	    ps.setString(2,entryForm.getJob_desc());
	    ps.setString(3,entryForm.getEmployer_name());
	    ps.setString(4,entryForm.getEmployer_url());
	    ps.setString(5,entryForm.getContact_name());
	    ps.setString(6,entryForm.getContact_info());
	    ps.setString(7,entryForm.getPosting_password());
	    ps.setDate(8,new java.sql.Date(System.currentTimeMillis()));
	    ps.setString(9,entryForm.getAdmin_contact_info());
	    executeUpdate(ps);
	  }
	  
	  /* Returns a JobBean with fields filled from the current row 
	   of the ResultSet parameter.
	  */
	  public JobBean fetchBeanFields(ResultSet rs) throws SQLException{
	    JobBean posting = new JobBean();
	    posting.setJob_number(""+rs.getInt(1));
	    posting.setJob_title(rs.getString(2));
	    posting.setJob_desc(rs.getString(3));
	    posting.setEmployer_name(rs.getString(4));
	    posting.setEmployer_url(rs.getString(5));
	    posting.setContact_name(rs.getString(6));
	    posting.setContact_info(rs.getString(7));
	    posting.setPosting_password(rs.getString(8));
	    posting.setLast_edit_date(rs.getDate(9));
	    posting.setAdmin_contact_info(rs.getString(10));
	    return posting;
	  }


	  /* Used when the expected ResultSet has exactly one row.  Returns a
	     ResultSet with the cursor positioned to that one row, or throws 
	     SQLException if more or less rows are found.
	  */
	  public ResultSet executeForOneRow(PreparedStatement ps)
	                      throws SQLException{
	    ResultSet rs = ps.executeQuery();
	    if(!rs.last()){
	      throw new SQLException(
	              "ResultSet has zero rows, from " + ps.toString());
	    }
	    if(rs.getRow() != 1){
	      throw new SQLException(
	              "ResultSet has other than one row, from " + ps.toString());
	    }
	    return rs;
	  }
	  
	    
	  /* Used for executeUpdate, expecting a result of 1.  
	   * Throws SQLException if resultInt not 1.
	   * In any case it closes the prepared statement before returning.
	   */
	  public void executeUpdate(PreparedStatement ps)
	        throws SQLException{ 
	    try{
	      int resultInt = ps.executeUpdate();
	      if (resultInt != 1){
	        throw new SQLException("resultInt should be 1 but is "+resultInt +
	          ", when attempting " + ps.toString());
	      }
	    }finally{
	      ps.close();
	    }
	  }
	}

